package com.roger.uia.helper.util;

import android.annotation.SuppressLint;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;


public class ExcelUtils {

    @SuppressWarnings("ResultOfMethodCallIgnored")
    public static void createExcel(String filePath, String[] title) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            HSSFSheet sheet = workbook.createSheet("Report");
            sheet.setDefaultColumnWidth((short)15);
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell;
            for (int i = 0; i < title.length; i++) {
                cell = row.createCell((short) i);
                cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                font.setFontHeightInPoints((short)10);
                font.setFontName("微软雅黑");
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                style.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                style.setFont(font);
                cell.setCellStyle(style);
                cell.setCellValue(title[i]);
            }
            File file = new File(filePath);
            if(!file.exists()){
                file.getParentFile().mkdirs();
                file.createNewFile();
            }
            FileOutputStream stream = new FileOutputStream(file);
            workbook.write(stream);
            stream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void addExcelData(String file,int sheet,int rows,short cols,String text) {
        try {
            File f = new File(file);
            FileInputStream fst=new FileInputStream(f);
            BufferedInputStream in = new BufferedInputStream(fst);
            POIFSFileSystem fs = new POIFSFileSystem(in);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet st = wb.getSheetAt(sheet);
            HSSFRow row = st.createRow(rows);
            HSSFCell cell = row.createCell(cols);
            cell.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell.setCellValue(text);
            FileOutputStream excelFileOutPutStream = new FileOutputStream(file);
            wb.write(excelFileOutPutStream);
            excelFileOutPutStream.flush();
            excelFileOutPutStream.close();
            in.close();
            fst.close();
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    public static int excelAllRows(String filePath,int sheet) {
        int Rows =0;
        try {
            File f = new File(filePath);
            FileInputStream fst=new FileInputStream(f);
            BufferedInputStream in = new BufferedInputStream(fst);
            POIFSFileSystem fs = new POIFSFileSystem(in);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet st = wb.getSheetAt(sheet);
            Rows = st.getLastRowNum();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return Rows;
    }

    @SuppressLint("SimpleDateFormat")
    public static String getExcelData(String file, int sheet, int rows, short cols)  {
        String value = "";
        try {
            File f = new File(file);
            FileInputStream fst=new FileInputStream(f);
            BufferedInputStream in = new BufferedInputStream(fst);
            POIFSFileSystem fs = new POIFSFileSystem(in);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet st = wb.getSheetAt(sheet);
            HSSFRow row = st.getRow(rows);
            HSSFCell cell= row.getCell(cols);
            if (cell != null) {
                cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            Date date = cell.getDateCellValue();
                            if (date != null) {
                                value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                            }
                        } else {
                            value = new DecimalFormat("0").format(cell.getNumericCellValue());
                        }
                        break;
                    case HSSFCell.CELL_TYPE_FORMULA:
                        if (!cell.getStringCellValue().equals("")) {
                            value = cell.getStringCellValue();
                        } else {
                            value = cell.getNumericCellValue() + "";
                        }
                        break;
                    case HSSFCell.CELL_TYPE_BLANK:
                        value = "";
                        break;
                    case HSSFCell.CELL_TYPE_ERROR:
                        value = "";
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                        value = (cell.getBooleanCellValue() ? "Y" : "N");
                        break;
                    default:
                        value = "";
                }
            }
            in.close();
            fst.close();
        }catch (IOException e){
            e.printStackTrace();
        }
        return value;
    }
}
